GoogleスプレッドシートのデータをS3へCSVとして保存する

#AWS

#Amazon S3

菅野昌孝

2017.11.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんばんは、菅野です。仕事で Google スプレッドシートを使う機会があるのですが、そのデータを AWS のサービスで使いたくなったので Google Apps Script（GAS）を使って S3 に CSV ファイルとして保存してみました。

はじめに

Google スプレッドシートは使ったことあるけど、Google Apps Script（GAS）は使ったことが無いという人がいるかもしれませんので始め方について書いておきます。まず、ブラウザは Google Chrome を用意してください。次に Google Apps Script アプリをインストールしましょう。以下の URL で見つかります。 https://chrome.google.com/webstore/search/Google%20Apps%20Script?hl=ja

作業の流れ

細かい事は追って説明しますが、作業全体の流れはこんな感じとなります。

スプレッドシートとデータを用意する
CSV データの保存場所として S3 バケットとフォルダを作成
S3 へ保存できる権限を持つ IAM ポリシーを作成
S3 へ保存するスクリプトが使うアクセスキーを作成
スクリプトを作成して実行する

データの準備

テスト用として果物の価格データを用意しました。シート名は「price-list」としておきます。

S3 に保存先を作成

既存でも新規でもいいので、S3 バケットの中にフォルダを作っておきます。今回はその中に「test-data」フォルダを作成しました。

また、保存するファイル名もここで決めておきましょう。シートの名前にしておくといいかもしれません。今回作成する CSV ファイルの名前は「price-list.csv」とします。

IAM ポリシーを作成

今回作成する GAS では S3 を扱うための外部ライブラリを利用するのですが、そのライブラリを使うためにはアクセスキーとシークレットアクセスキーが必要になります。万が一アクセスキーが漏洩しても被害を最小限とするためにも、権限は最低限としておきます。 IAM ポリシーはこんな感じで作成しました。

IAM ユーザーを作成

アクセスキーとシークレットアクセスキーが必要なので IAM ユーザーを作成します。作成する IAM ユーザーはプログラムによるアクセスのみが可能で、先ほど作成した IAM ポリシーを適用します。

スクリプトに使う情報を集める

ここからが本番です。必要な情報を先に集めておきましょう。

スプレッドシートのID

スプレッドシートの URL の一部が ID となっています。

アクセスキーとシークレットアクセスキー

先ほど作成した IAM ユーザーのアクセスキーとシークレットアクセスキーです。

保存先

保存先として必要なバケット名・フォルダ名・ファイル名です。フォルダ名とファイル名をつなげた文字が必要になります。今回の場合はこうなります。「test-data/price-list.csv」

スクリプトを作成

スクリプトファイルの作成

スプレッドシートのメニューからスクリプトを呼び出します。
「ツール」-「スクリプトエディタ」をクリックします。
左上をクリックしてプロジェクト名を決めます。
以下のスクリプトを貼り付けます

function myFunction() {
// 使ったS3ライブラリ
// MB4837UymyETXyn8cv3fNXZc9ncYTrHL9

// スプレッドシートを取得
var ss = SpreadsheetApp.openById('スプレッドシートのID');

// シートのオブジェクトを取得
var sheet = ss.getSheetByName('price-list');

// データを取得
var data = sheet.getRange('A:C').getValues();

// 送信データ用の配列を用意
var csv = '';

// データをチェックしながらループ
for ( var i = 0; i < data.length; i++ )
{
// id があれば
if ( data[i][0] != '' )
{
// データを作成
csv += '"' + data[i][0] + '","' + data[i][1] + '","' + data[i][2] + '"' + "\n";
}
}

// バイナリに変換
csv = Utilities.newBlob( csv );

var s3 = S3.getInstance( 'アクセスキー', 'シークレットアクセスキー' );
s3.putObject( 'バケット名', 'test-data/price-list.csv', csv, {logRequests:true} );
}

ライブラリの読み込み

今回は外部ライブラリを利用します。 Amazon S3 API Binding for Google Apps Script まずメニューの「リソース」-「ライブラリ」をクリックします。

ポップアップが表示されたら以下の手順を実行してください。

「MB4837UymyETXyn8cv3fNXZc9ncYTrHL9」と入力する
「追加」ボタンをクリックする
バージョンを選択する
「保存」ボタンをクリックする

実行してみる

上にある右向きの三角が実行ボタンですのでクリックしてみます。数秒で実行が完了し以下のようにファイルが作成されました。中身は以下のようになっています。

今回苦労した部分

今回のスクリプトの作成日数は全部で3日、その内以下の1行を見つけるまでに2日掛かりました。私の中でこのスクリプトの最重要部分です。

// バイナリに変換
csv = Utilities.newBlob( csv );

この1行が無いとテキストファイル全体がダブルクォーテーションで囲まれてしまい、目的とするデータが完成しませんでした。

さいごに

いかがでしたでしょうか。今回作成したスクリプトはトリガーを作成することで、スプレッドシートが変更された時や指定した時間に自動で実行させる事が可能です。また、スプレッドシートのデータを S3 にアップロードするだけでそれをトリガーとして Lambda を起動する事も可能ですし、 Athena を使って SQL ライクに検索する事も可能になります。

現在スプレッドシートで管理している情報があれば、その内容を手軽に AWS で利用してみませんか？今回のブログエントリーがそのお役に立てれば幸いです。